﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Text;
using ReflectionStudio.Core.Events;
using INTEX.DataBase.Tools;
namespace WPFExcelReport
{

    public class JLTWB14KSData : BaseFunction
    {
        
        public JLTWB14KSData()
            : base()
        {

        }
        public JLTWB14KSData(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {
        }

        public DataSet GetWBBEW(DataRow dwABPOS)
        {
            DataSet Result;
            DataSet Return = new DataSet();
            string TableName = "WBBEW";
            string ART = dwABPOS["ART"].ToString().Trim();
            string CUST = dwABPOS["CUST"].ToString().Trim();
            string STAFF = dwABPOS["STAFF"].ToString().Trim();
            string FROMDNDATE = dwABPOS["FROMDNDATE"].ToString().Trim();
            string ENDDNDATE = dwABPOS["ENDDNDATE"].ToString().Trim();
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "select WBEWBUCHDAT,WBEWART,WBEWFARBE, "
                    + " TRIM(DECODE(WBEWBS,'34',WBEWMTR,'35',-WBEWMTR)*0.001) AS MTR, "
                    + " B.KSTKSTKRS AS STAFF,TO_NUMBER(B.KSTKST) AS CUSTID,TRIM(B.KSTBEZ) AS CUSTOMER "
                    + " from wbbew A, KSTSTELL B "
                    + " where wbewlb = 'KS' AND A.WBEWMANDANT = B.KSTMANDANT "
                    + " AND SUBSTR(A.WBEWLP,1,2) = B.KSTKSTKRS "
                    + " AND SUBSTR(WBEWLP,3,4) = B.KSTKST "
                    + " AND WBEWBS IN ('34','35') "
                    + " AND (A.WBEWBUCHDAT >= " + FROMDNDATE + " AND  A.WBEWBUCHDAT <= " + ENDDNDATE + ") "  
                    + " AND A.WBEWART LIKE '%"+ART+"%' "
                    + " AND (B.KSTKSTKRS = '" + STAFF + "' OR '" + STAFF + "' = 'AA' ) "
                    + " AND (B.KSTKST = '" + CUST + "' OR '" + CUST + "' = '0000' ) "
                    + " AND  (SUBSTR(WBEWLP,1,1),TO_NUMBER(SUBSTR(WBEWLP,3,4))) IN  "
                    + " (SELECT DISTINCT TRIM(KDVVB),TO_NUMBER(KDVKUNDNR)  "
                    + " FROM KUNDVER WHERE TRIM(KDVVB) IS NOT NULL)"
                    ;

                Result = Connect2Proxy(orlReadCommand);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = TableName;
                    byte[] b;
                    string value;
                    foreach (DataRow dw in Result.Tables[TableName].Rows)
                    {
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["CUSTOMER"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["CUSTOMER"] = value;
                    }
                }
                Return.Merge(Result);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return Return;
        }       
        public DataSet BuildJLTWB14KSData(DataRow dwUI)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {
                setResult = GetWBBEW(dwUI);
                setReturn.Merge(setResult);                
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
    }
}